CREATE PROCEDURE sp_AddNewAdvertise
@name nvarchar(50),
@content ntext,
@position varchar(50),
@isFrontPage bit
AS
	INSERT INTO Advertise
	VALUES (@name, @content, @position,@isFrontPage)

GO

CREATE PROCEDURE sp_UpdateAdvertise
@id int,
@name nvarchar(50),
@content ntext,
@position varchar(50),
@isFrontPage bit
AS
	UPDATE Advertise
	SET [Name] = @name, [Content] = @content, 
		Position = @position, IsFrontPage = @isFrontPage
	WHERE ID = @id
GO

CREATE PROCEDURE sp_DeleteAdvertise
@id int
AS
	DELETE Advertise
	WHERE ID = @id

GO

CREATE PROCEDURE sp_ViewAllAdvertise
AS
	SELECT * FROM Advertise

GO

CREATE PROCEDURE sp_ViewAdvertiseByID
@id int
AS
	SELECT * FROM Advertise
	WHERE ID = @id

GO

CREATE PROCEDURE sp_AddNewCheckIn
@id int OUTPUT,
@fullname nvarchar(255),
@phone varchar(50),
@birthday datetime,
@email varchar(255),
@company nvarchar(255),
@note nvarchar(255)
AS
	INSERT INTO CheckIn
	VALUES (@fullname, @phone,@birthday,@email,@company,@note)
	SET @id = Scope_Identity()

GO

CREATE PROCEDURE sp_AddNewReg
@checkinID int,
@courseID int
AS
	INSERT INTO RegisterCourse
	VALUES (@checkinID, @courseID)

GO

CREATE PROCEDURE sp_DeleteCheckIn
@id int
AS
	DELETE CheckIn 
	WHERE ID = @id
	DELETE RegisterCourse WHERE CheckInID = @id
	
GO

CREATE PROCEDURE sp_UpdateCheckIn
@id int,
@fullname nvarchar(255),
@phone varchar(50),
@birthday datetime,
@email varchar(255),
@company nvarchar(255),
@note nvarchar(255)
AS
	UPDATE CheckIn 
	SET FullName = @fullname, Phone = @phone, DateOfBirth = @birthday,
		Email = @email, Company = @company, Note = @note
	WHERE ID = @id
GO

CREATE PROCEDURE sp_UpdateReg
@id int,
@courseID int
AS
	UPDATE RegisterCourse 
	SET CourseID = @courseID
	WHERE ID = @id 

GO

CREATE PROCEDURE sp_GetCourseName
@id int
AS
	SELECT Course.ID, [Name] FROM Course 
	INNER JOIN RegisterCourse ON Course.ID = RegisterCourse.CourseID
	INNER JOIN CheckIn ON RegisterCourse.CheckInID = CheckIn.ID
	WHERE CheckIn.ID = @id

GO

CREATE PROCEDURE sp_GetCourseName2
@id int
AS 
	SELECT Course.ID,[Name] FROM Course 
	WHERE [Name] not in (SELECT [Name] FROM Course 
						INNER JOIN RegisterCourse ON Course.ID = RegisterCourse.CourseID 
						INNER JOIN CheckIn ON RegisterCourse.CheckInID = CheckIn.ID	
						WHERE CheckIn.ID =@id )

GO

CREATE PROCEDURE sp_GetRegID
@checkID int,
@courseID int
AS
	SELECT ID FROM RegisterCourse
	WHERE CheckInID= @checkID AND CourseID = @courseID

GO

CREATE PROCEDURE sp_DeleteReg
@checkID int
AS
	DELETE RegisterCourse
	WHERE CheckInID = @checkID

GO
--select * from RegisterCourse
--select * from CheckIn

GO

CREATE PROCEDURE sp_DeleteAdmin
@id int
AS
	DELETE Administrator 
	WHERE ID = @id

GO

CREATE PROCEDURE sp_changePassword
@id int,
@newpass varchar(MAX)
AS
	UPDATE Administrator
	SET Password = @newpass
	WHERE ID = @id
GO
--SELECT * FROM Administrator
